Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Comparison Conditions

Comparison conditions compare one expression with another. The result of such a comparison can be `TRUE`, `FALSE`, or `NULL`.

Large objects (LOBs) are not supported in comparison conditions. However, you can use PL/SQL programs for comparisons on `CLOB` data.

When comparing numeric expressions, Oracle uses numeric precedence to determine whether the condition compares `NUMBER`, `BINARY_FLOAT`, or `BINARY_DOUBLE` values. Please refer to "Numeric Precedence" for information on numeric precedence.

Two objects of nonscalar type are comparable if they are of the same named type and there is a one-to-one correspondence between their elements. In addition, nested tables of user-defined object types, even if their elements are comparable, must have `MAP` methods defined on them to be used in equality or `IN` conditions.

See Also: map_order_func_declaration for more information on `MAP` methods PL/SQL User's Guide and Reference for the requirements for comparing user-defined object types in PL/SQL

Table 7-2 lists comparison conditions.

Table 7-2 Comparison Conditions

Type of Condition | Purpose | Example
--- | --- | ---
    =
| Equality test. |
    SELECT *
      FROM employees
      WHERE salary = 2500;
    !=
    ^=
    < >
    ÿ=
| Inequality test. Some forms of the inequality condition may be unavailable on some platforms. |
    SELECT *
      FROM employees
      WHERE salary != 2500;
    >

    <
| Greater-than and less-than tests. |
    SELECT * FROM employees
      WHERE salary > 2500;
    SELECT * FROM employees
      WHERE salary < 2500;
    >=

    <=
| Greater-than-or-equal-to and less-than-or-equal-to tests. |
    SELECT * FROM employees
      WHERE salary >= 2500;
    SELECT * FROM employees
      WHERE salary <= 2500;
    ANY
    SOME
| Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values. Evaluates to `FALSE` if the query returns no rows. |
    SELECT * FROM employees
      WHERE salary = ANY
      (SELECT salary
       FROM employees
      WHERE department_id = 30);
    ALL
| Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values. Evaluates to `TRUE` if the query returns no rows. |
    SELECT * FROM employees
      WHERE salary >=
      ALL ( 1400, 3000);

## Simple Comparison Conditions

A simple comparison condition specifies a comparison with expressions or subquery results.

simple_comparison_condition::=

Description of the illustration simple_comparison_condition.gif

expression_list::=

Description of the illustration expression_list.gif

If you use the lower form of this condition (with multiple expressions to the left of the operator), then you must use the lower form of the `expression_list`, and the values returned by the subquery must match in number and datatype the expressions in `expression_list`.

See Also: "Expression Lists" for more information about combining expressions and SELECT for information about subqueries

## Group Comparison Conditions

A group comparison condition specifies a comparison with any or all members in a list or subquery.

group_comparison_condition::=

Description of the illustration group_comparison_condition.gif

expression_list::=

Description of the illustration expression_list.gif

If you use the upper form of this condition (with a single expression to the left of the operator), then you must use the upper form of `expression_list`. If you use the lower form of this condition (with multiple expressions to the left of the operator), then you must use the lower form of `expression_list`, and the expressions in each `expression_list` must match in number and datatype the expressions to the left of the operator.

See Also: "Expression Lists" SELECT